#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
day 61 homework
"""

import os
import django
from orm_practice import models
from django.db.models import Max, Min, Avg, Sum, Count, F, Q

os.environ.setdefault("DJANGO_SETTINGS_MODULE", "day61.settings")
django.setup()



# 查找所有书名里包含金老板的书
'''
book_list = models.Book.objects.filter(title__contains='金老板').values('title')
for book in book_list:
    print(book['title'])
# select title from orm_practice_book where title like '%金老板%';
'''

# 查找出版日期是2018年的书
'''
book_list = models.Book.objects.filter(publish_date__year='2018').values('title')
for book in book_list:
    print(book['title'])
# select title from orm_practice_book where publish_date like '2018%';
'''

# 查找出版日期是2017年的书名
'''
book_list = models.Book.objects.filter(publish_date__year='2017').values('title')
for book in book_list:
    print(book.title)
# select title from orm_practice_book where publish_date like '2017%';
'''

# 查找价格大于10元的书
'''
book_list = models.Book.objects.filter(price__gt=10).values('title')
for book in book_list:
    print(book['title'])
# select title from orm_practice_book where price > 10;
'''

# 查找价格大于10元的书名和价格
'''
book_list = models.Book.objects.filter(price__gt=10).values('title', 'price')
for book in book_list:
    print(book['title'], book['price'])
# select title,price from orm_practice_book where price > 10;
'''

# 查找memo字段是空的书
'''
book_list = models.Book.objects.filter(memo__isnull=True).values('title')
for book in book_list:
    print(book['title'])
# select title from orm_practice_book where memo is null;
'''

# 查找在北京的出版社
'''
pub_list = models.Publisher.objects.filter(city='北京').values('name')
for pub in pub_list:
    print(pub['name'])
# select name from orm_practice_publisher where city='北京';
'''

# 查找名字以沙河开头的出版社
'''
pub_list = models.Publisher.objects.filter(name__startswith='沙河').values('name')
for pub in pub_list:
    print(pub['name'])
# select name from orm_practice_publisher where name like '沙河%';
'''

# 查找“沙河出版社”出版的所有书籍
'''
pub_list = models.Publisher.objects.filter(name='沙河出版社').values_list('id')
book_list = models.Book.objects.filter(publisher_id__in=pub_list).values('title')
for book in book_list:
    print(book['title'])
# select title from orm_practice_book where publisher_id in (
# select id from orm_practice_publisher where name = '沙河出版社'
# );
'''

# 查找每个出版社出版价格最高的书籍价格
'''
pub_list = models.Publisher.objects.all().values('id', 'name')
for pub in pub_list:
    print('*********{}*********'.format(pub['name']))
    book = models.Book.objects.filter(publisher_id=pub['id']).order_by('-price').values('title', 'price').first()
    if book:
        print(book['title'], book['price'])
    else:
        print('null')

pub_list = models.Publisher.objects.all()
for pub in pub_list:
    print('*******{}******'.format(pub.name))
    book = pub.book_set.all().order_by('-price').first()
    if book:
        print(book.title, book.price)
    else:
        print('null')
# select title,max(price) from orm_practice_book group by publisher_id;
'''

# 查找每个出版社的名字以及出的书籍数量
'''
pub_list = models.Publisher.objects.all()
for pub in pub_list:
    print(pub.name, pub.book_set.all().count())

pub_list = models.Publisher.objects.all().values('id', 'name')
for pub in pub_list:
    book_count = models.Book.objects.filter(publisher_id=pub['id']).count()
    print(pub['name'], book_count)

pub_list = models.Publisher.objects.all()
for pub in pub_list:
    print(pub.name, pub.book_set.all().count())
# select name,count(title) from orm_practice_book as book right join orm_practice_publisher as pub 
# on book.publisher_id = pub.id group by pub.id;
'''

# 查找作者名字里面带“小”字的作者
'''
author_list = models.Author.objects.filter(name__contains='小').values('name')
for author in author_list:
    print(author['name'])
# select name from orm_practice_author where name like '%小%';
'''

# 查找年龄大于30岁的作者
'''
author_list = models.Author.objects.filter(age__gt=30).values('name')
for author in author_list:
    print(author['name'])
# select name from orm_practice_author where age > 30;
'''

# 查找手机号是155开头的作者
'''
author_list = models.Author.objects.filter(phone__startswith='155').values('name')
for author in author_list:
    print(author['name'])
# select name from orm_practice_author where phone like '155%';
'''

# 查找手机号是155开头的作者的姓名和年龄
'''
author_list = models.Author.objects.filter(phone__startswith='155').values('name', 'age')
for author in author_list:
    print(author['name'], author['age'])
# select name,age from orm_practice_author where phone like '155%';
'''

# 查找每个作者写的价格最高的书籍价格
'''
author_list = models.Author.objects.annotate(max=Max('book__price')).values('name', 'max')
for author in author_list:
    print(author['name'], author['max'])
# select name,max(price) from orm_practice_author author right join
# (select author_id,price from orm_practice_book_author left join orm_practice_book book 
# on orm_practice_book_author.book_id = book.id) book
# on author.id = book.author_id group by author.id;
'''

# 查找每个作者的姓名以及出的书籍数量
'''
author_list = models.Author.objects.annotate(count=Count('book')).values('name', 'count')
for author in author_list:
    print(author['name'], author['count'])
# select name,count(book_id)
# from orm_practice_author author right join orm_practice_book_author opba 
# on author.id = opba.author_id group by author.id;
'''

# 查找书名是“跟金老板学开车”的书的出版社
'''
pub_list = models.Publisher.objects.filter(book__title='跟金老板学开车').values('name')
for pub in pub_list:
    print(pub['name'])
# select name from orm_practice_publisher pub right join orm_practice_book book 
# on pub.id = book.publisher_id where title='跟金老板学开车';
'''

# 查找书名是“跟金老板学开车”的书的出版社所在的城市
'''
pub_list = models.Publisher.objects.filter(book__title='跟金老板学开车').values('city')
for pub in pub_list:
    print(pub['city'])
# select city from orm_practice_publisher pub right join orm_practice_book book 
# on pub.id = book.publisher_id where title='跟金老板学开车';
'''

# 查找书名是“跟金老板学开车”的书的出版社的名称
'''
pub_list = models.Publisher.objects.filter(book__title='跟金老板学开车').values('name')
for pub in pub_list:
    print(pub['name'])
# select name from orm_practice_publisher pub right join orm_practice_book book 
# on pub.id = book.publisher_id where title='跟金老板学开车';
'''

# 查找书名是“跟金老板学开车”的书的出版社出版的其他书籍的名字和价格
'''
book_list = models.Book.objects.filter(
    Q(publisher_id__in=(models.Publisher.objects.filter(book__title='跟金老板学开车'))) & ~Q(title='跟金老板学开车')
).values('title', 'price')
for book in book_list:
    print(book['title'], book['price'])
# select title,price from orm_practice_book where publisher_id in (
# select pub.id from orm_practice_publisher pub right join orm_practice_book book 
# on pub.id = book.publisher_id where title = '跟金老板学开车')
# and title != '跟金老板学开车';
'''

# 查找书名是“跟金老板学开车”的书的所有作者
'''
author_list = models.Author.objects.filter(book__title='跟金老板学开车').values('name')
for author in author_list:
    print(author['name'])
# select name from orm_practice_author author right join (
# select title,author_id from orm_practice_book book right join orm_practice_book_author opba 
# on book.id = opba.book_id where title = '跟金老板学开车'
# ) book on author.id = book.author_id;
'''

# 查找书名是“跟金老板学开车”的书的作者的年龄
'''
author_list = models.Author.objects.filter(book__title='跟金老板学开车').values('name', 'age')
for author in author_list:
    print(author['name'], author['age'])
# select name,age from orm_practice_author author right join (
# select title,author_id from orm_practice_book book right join orm_practice_book_author opba 
# on book.id = opba.book_id where title = '跟金老板学开车'
# ) book on author.id = book.author_id;
'''

# 查找书名是“跟金老板学开车”的书的作者的手机号码
'''
author_list = models.Author.objects.filter(book__title='跟金老板学开车').values('name', 'phone')
for author in author_list:
    print(author['name'], author['phone'])
# select name,phone from orm_practice_author author right join (
# select title,author_id from orm_practice_book book right join orm_practice_book_author opba 
# on book.id = opba.book_id where title = '跟金老板学开车'
# ) book on author.id = book.author_id;
'''

# 查找书名是“跟金老板学开车”的书的作者们的姓名以及出版的所有书籍名称和价钱
'''
book_list = models.Book.objects.filter(author__in=(models.Author.objects.filter(book__title='跟金老板学开车'))).values(
    'author__name', 'title', 'price')
for book in book_list:
    print(book['author__name'], book['title'], book['price'])
# select name, title, price
# from orm_practice_author author inner join (
#   select author_id,title,price from orm_practice_book_author apba inner join 
#   orm_practice_book opb on apba.book_id = opb.id
#   ) book on author.id = book.author_id
# where author_id in (
#   select author.author_id
#   from orm_practice_book book
#          left join (
#     select book_id, author_id, name
#     from orm_practice_author author
#            right join orm_practice_book_author opba on author.id = opba.author_id
#   ) author
#                    on author.book_id = book.id
#   where title = '跟金老板学开车'
# );
'''
